package edu.unika.aifb.graphindex.query;

import java.io.IOException;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.StringTokenizer;

import net.disy.cadenza.repository.selector.ResultTable;
import net.disy.cadenza.repository.selector.TableCondition;

import org.openrdf.model.vocabulary.RDF;

import edu.unika.aifb.graphindex.importer.db.ConnectionPool;
import edu.unika.aifb.graphindex.importer.db.metadata.Column;
import edu.unika.aifb.graphindex.importer.db.metadata.Database;
import edu.unika.aifb.graphindex.importer.db.metadata.Table;
import edu.unika.aifb.graphindex.index.DataIndex;
import edu.unika.aifb.graphindex.index.IndexReader;
import edu.unika.aifb.graphindex.searcher.keyword.model.KeywordQNode;
import edu.unika.aifb.graphindex.storage.DataField;
import edu.unika.aifb.graphindex.storage.IndexDescription;
import edu.unika.aifb.graphindex.storage.IndexStorage;
import edu.unika.aifb.graphindex.storage.IndexStorageReader;
import edu.unika.aifb.graphindex.storage.StorageException;

public class SQLQuery {
	private StructuredQuery structuredQuery;
	private Database database;
	private Map<String, String> variableToTableName;
	private String sqlQuery;
	
	public SQLQuery(StructuredQuery structuredQuery, Database database){
		this.structuredQuery = structuredQuery;
		this.database = database;
		init();
	}
	
	
	
	public String getSqlQuery() {
		return sqlQuery;
	}



	public Map<String, TableCondition> getTableConstraints(edu.unika.aifb.graphindex.data.Table<String> result, IndexReader ir) throws StorageException, IOException{
		Map<String, TableCondition> tableConstraints = new HashMap<String, TableCondition>();
		
		DataIndex dataIndex = ir.getDataIndex();
		IndexStorage spIndex =  ir.getStructureIndex().getSPIndexStorage();
		IndexStorageReader dataIndexReader = dataIndex.getIndexStorageReader(IndexDescription.SPO);
		for(QueryEdge queryEdge : structuredQuery.getQueryGraph().edgeSet()){
			if(queryEdge.getLabel().equalsIgnoreCase(RDF.TYPE.toString())){
				continue;
			}
			
			String edgeName = queryEdge.getLabel().substring(queryEdge.getLabel().indexOf("#") + 1);
			
			
			QNode sourceNode = queryEdge.getSource();
			QNode targetNode = queryEdge.getTarget();
			Map<String, String> extToDataProeprties = new HashMap<String, String>();
			if(targetNode instanceof KeywordQNode){
				List<String[]> rows = result.getRows();
				int column = result.getColumn(sourceNode.getLabel());
				String tableName = edgeName.substring(0, edgeName.lastIndexOf("."));
				String columnName = edgeName.substring(edgeName.lastIndexOf(".") + 1);
				Table table = database.getTable(tableName);
				TableCondition tableConstraint = tableConstraints.get(tableName);
				if(tableConstraint == null){
					tableConstraint = new TableCondition(table.getName(), columnName);
					tableConstraints.put(tableName, tableConstraint);
					for(String[] row : rows){
						String entity = row[column];
						String ext = spIndex.getDataItem(IndexDescription.SES, DataField.EXT_SUBJECT, entity);
						String entityDataProperties = extToDataProeprties.get(ext);
						if(entityDataProperties == null){
							 entityDataProperties = spIndex.getDataItem(IndexDescription.EXTDP, DataField.EXT_DP, ext);
							 extToDataProeprties.put(ext, entityDataProperties);
						}
						StringTokenizer st = new StringTokenizer(entityDataProperties, "\n");
						while(st.hasMoreTokens()){
							String property = st.nextToken();
							if(property.equalsIgnoreCase(RDF.TYPE.stringValue()))
								continue;
							String object  = dataIndexReader.getDataItem(entity, property);
							tableConstraint.addColumnValue(property.substring(property.lastIndexOf(".") + 1), object);
						}
					}
				}else{
					tableConstraint.addColumn(columnName);
				}
			}
			
		}
		return tableConstraints;
	}
	
	public ResultTable getResultTable(ConnectionPool connectionPool) throws SQLException{
		long start = System.currentTimeMillis();
		ResultSet resultSet = connectionPool.executeQuery(sqlQuery, 50);
		System.out.println("HIER " + (System.currentTimeMillis() - start));
		
		if(resultSet == null){
			return null;
		}
		
		ResultSetMetaData resultSetMetadata = resultSet.getMetaData();
		int columnCount = resultSetMetadata.getColumnCount();
		String[] tableColumns = new String[columnCount];
		for(int i = 1; i <= columnCount; i++){
			String variableColumnName = resultSetMetadata.getColumnName(i);
			String variableName = variableColumnName.substring(0, variableColumnName.indexOf("."));
			String tableName = variableToTableName.get(variableName);
			tableColumns[i-1] = variableColumnName.replace(variableName, tableName);
		}
		
		Map<String, List<String>> tableColumnToValues = new HashMap<String, List<String>>();
		for(int i = 0; i < tableColumns.length; i++)
			tableColumnToValues.put(tableColumns[i], new  ArrayList<String>());
		
		boolean hasRows = false;
		while(resultSet.next()){
			hasRows = true;
			for(int i = 1; i <= columnCount; i++){
				tableColumnToValues.get(tableColumns[i-1]).add(resultSet.getString(i));
			}
		}
		resultSet.close();
		
		if(!hasRows)
			return null;
		
		ResultTable resultTable = new ResultTable(tableColumnToValues);
		
		for(QueryEdge queryEdge : structuredQuery.getQueryGraph().edgeSet()){
			if(queryEdge.getLabel().equalsIgnoreCase(RDF.TYPE.toString())){
				continue;
			}
			
			String tableColumn = queryEdge.getLabel().substring(queryEdge.getLabel().indexOf("#") + 1);
			QNode targetNode = queryEdge.getTarget();
			
			if(targetNode instanceof KeywordQNode){
				KeywordQNode keywordNode = (KeywordQNode)targetNode;
				String keywords = "";
				for(String keyword : keywordNode.getKeywords())
					keywords += (keyword + " ");
				resultTable.addKeywordToTableColumn(keywords.trim(), tableColumn);
			}
		}
		
		return resultTable;
	}
	
	private void init(){
		StringBuffer whereCluse = new StringBuffer(" where ");
		variableToTableName = new HashMap<String, String>();
		for(QueryEdge queryEdge : structuredQuery.getQueryGraph().edgeSet()){
			String edgeName = queryEdge.getLabel().substring(queryEdge.getLabel().indexOf("#") + 1);
			if(queryEdge.getLabel().equalsIgnoreCase(RDF.TYPE.toString())){
				QNode sourceNode = queryEdge.getSource();
				KeywordQNode targetNode = (KeywordQNode)queryEdge.getTarget();
				variableToTableName.put(sourceNode.getLabel().substring(1), database.getTable(targetNode.getKeywords().iterator().next()).getName());
				continue;
			}
			QNode sourceNode = queryEdge.getSource();
			QNode targetNode = queryEdge.getTarget();
	
			if(sourceNode.isSelectVariable() && targetNode.isSelectVariable()){
				Table sourceTable = database.getSourceTable(edgeName);
				Table targetTable = database.getTargetTable(edgeName);
				if(sourceTable == null || targetTable == null)
					continue;
				String sourceVariable = sourceNode.getLabel().substring(1);
				String targetVariable = targetNode.getLabel().substring(1);
				variableToTableName.put(sourceVariable, sourceTable.getName());
				variableToTableName.put(targetVariable, targetTable.getName());
				List<Column> sourceColumns = database.getSourceTable(edgeName).getForeignKeyConstraint(edgeName).getSourceColumns();
				List<Column> targetColumns = database.getSourceTable(edgeName).getForeignKeyConstraint(edgeName).getTargetColumns();
				for(int i = 0 ; i < sourceColumns.size(); i++ ){
					whereCluse.append(sourceVariable).append(".").append(sourceColumns.get(i).getName());
					whereCluse.append(" = ");
					whereCluse.append(targetVariable).append(".").append(targetColumns.get(i).getName());
					whereCluse.append(" AND ");
				}
			}else{
				if(targetNode instanceof KeywordQNode){
					KeywordQNode keywordNode = (KeywordQNode)targetNode;
					boolean isNumber = false;
					String tableName = edgeName.substring(0, edgeName.indexOf("."));
					String columnName = edgeName.substring(edgeName.indexOf(".") + 1);
					Column column = database.getTable(tableName).getColumn(columnName);
					String sourceVariable = sourceNode.getLabel().substring(1);
					if(!variableToTableName.containsKey(sourceVariable))
						variableToTableName.put(sourceVariable, tableName);
					String type = column.getType();
					System.out.println(tableName + " " +columnName + " " + type + " " + keywordNode);
					if(type != null)
						isNumber = isNumber(type);
					else
						isNumber = isNumber(column.getDataType());
					String variableColumName = edgeName.replaceFirst(edgeName.substring(0, edgeName.indexOf(".")), sourceNode.getLabel().substring(1));
					String value;
					Iterator<String> iter = keywordNode.getKeywords().iterator();
					while(iter.hasNext()){
						value = iter.next();
						if(isNumber){
							whereCluse.append(variableColumName);
							whereCluse.append(" = ");
							whereCluse.append(value);
						}else{
							whereCluse.append("LOWER(").append(variableColumName).append(")");
							whereCluse.append(" like ");
							whereCluse.append("LOWER('%");
							whereCluse.append(value);
							whereCluse.append("%')");
						}
						whereCluse.append(" AND ");
					}
				}
			}
		}
		if(whereCluse.toString().endsWith(" AND "))
			whereCluse.replace(whereCluse.length() - 5, whereCluse.length(), "");
		
		StringBuffer selectQuery = new StringBuffer("select ");
		
		for(String variableName : variableToTableName.keySet()){
			String tableName = variableToTableName.get(variableName);
			for(Column column : database.getTable(tableName).getColumns()){
				if(column.isVisible() || column.isPrimaryKey()){
					String variableColumnName = variableName + "." + column.getName();
					selectQuery.append(variableColumnName).append(" as ").append("\"" + variableColumnName + "\"").append(", ");
				}
			}
		}
		if(selectQuery.toString().endsWith(", "))
			selectQuery.delete(selectQuery.length() - 2, selectQuery.length());
		
		selectQuery.append(" from ");
		for(String variable : variableToTableName.keySet()){
			selectQuery.append(variableToTableName.get(variable));
			selectQuery.append(" ");
			selectQuery.append(variable);
			selectQuery.append(", ");
		}
		if(selectQuery.toString().endsWith(", "))
			selectQuery.delete(selectQuery.length() - 2, selectQuery.length());
		
		this.sqlQuery = selectQuery.append(whereCluse).toString();
		System.out.println(sqlQuery);
	}
	
	private  boolean isNumber(int dataType){
		return (dataType == Types.BIGINT || dataType == Types.DECIMAL || dataType == Types.DOUBLE || dataType == Types.FLOAT ||dataType == Types.INTEGER ||dataType == Types.NUMERIC);
	}
	
	private  boolean isNumber(String dataType){
		return (dataType.equalsIgnoreCase("int") || dataType.equalsIgnoreCase("double") || dataType.equalsIgnoreCase("long") || dataType.equalsIgnoreCase("BigInteger") || dataType.equalsIgnoreCase("float")||dataType.equalsIgnoreCase("BigDecimal"));
	}
}
